﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Student
{
    static class QueryUtility
    {
        static string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StudentDB.mdf;Integrated Security=True;User Instance=True";
        public static bool ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        //return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        return false;
                        throw new Exception(E.Message);
                    }
                    return true;
                }
            }
        }

        //执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。
        public static bool ExecuteInsert(string sql, SqlParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    if (parameters != null) cmd.Parameters.AddRange(parameters);
                    cmd.ExecuteNonQuery();
                    //cmd.CommandText = @"select LAST_INSERT_ID()";
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }
        public static bool ExecuteInsert(string sql)
        {
            return ExecuteInsert(sql, null);
        }

        //<return>SqlDataReader
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }

        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static DataTable GetTable(string sql, params SqlParameter[] pas)
        {

            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, connection);
                try
                {
                    cmd.CommandText = sql;
                    connection.Open();
                    cmd.ExecuteNonQuery();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    cmd.Parameters.Clear();

                    foreach (SqlParameter temppa in pas)
                    {
                        cmd.Parameters.Add(temppa);
                    }
                    da.Fill(ds);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return ds.Tables[0] ?? new DataTable();
        }
    }
}
